package com.open.exam.sqlprovider;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * @author 奶茶
 * Created by on 2017/11/22.
 */
public class ExamPlanInfoProvider {

    private static final Logger logger = LoggerFactory.getLogger(ExamPlanInfoProvider.class);

    public String findExamPlanDetailViewModelListBySomeWhere(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder();
        String planId = (String) parameter.get("planId");
        String roomId = (String) parameter.get("roomId");
        String sceneId = (String) parameter.get("sceneId");
        String subjectId = (String) parameter.get("subjectId");
        String appId = (String) parameter.get("appId");
        String siteId = (String) parameter.get("siteId");

        sb.append("SELECT dataRoom.ExamRoomID,dataScene.ExamSceneID,dataSubject.ExamSubjectID SubjectID,dataPlan.ExamPlanInfoID,dataPlan.BeginTime,dataPlan.EndTime,dataScene.ExamSceneName,dataRoom.ExamRoomName,dataSubject.SubjectName,dataArrangeDistinct.count ExamStudentCount,dataScene.PlanBeginTime,dataScene.PlanBeginTime SceneBeginTime,dataSubject.TimeLimits FROM ");
        sb.append("( SELECT dataArrange.ExamPlanInfoID,dataArrange.ExamSubjectID,dataArrange.AppID,dataArrange.ExamSceneID,dataArrange.ExamRoomID,COUNT(1) AS count FROM ExamineeArrange AS dataArrange WHERE 1=1");
        if(planId != null){
            sb.append(" AND dataArrange.ExamPlanInfoID = \'").append(planId).append("\' ");
        }
        if(roomId != null){
            sb.append(" AND dataArrange.ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(sceneId != null){
            sb.append(" AND dataArrange.ExamSceneID = \'").append(sceneId).append(" \'");
        }
        if(subjectId != null){
            sb.append(" AND dataArrange.ExamSubjectID = \'").append(subjectId).append("\' ");
        }
        if(appId != null && !("").equals(appId)){
            sb.append(" AND dataArrange.AppID = \'").append(appId).append("\'");
        }
        sb.append(" GROUP BY dataArrange.ExamPlanInfoID,dataArrange.AppID,dataArrange.ExamSubjectID,dataArrange.ExamSceneID,dataArrange.ExamRoomID) AS dataArrangeDistinct");
        sb.append(" JOIN ExamScenes AS dataScene ON dataArrangeDistinct.ExamSceneID = dataScene.ExamSceneID");
        sb.append(" JOIN ExamSubject AS dataSubject ON dataArrangeDistinct.ExamSubjectID = dataSubject.ExamSubjectID");
        sb.append(" JOIN ExamRoom AS dataRoom ON dataArrangeDistinct.ExamRoomID = dataRoom.ExamRoomID");
        sb.append(" JOIN ExamPlanInfo AS dataPlan ON dataArrangeDistinct.ExamPlanInfoID = dataPlan.ExamPlanInfoID");
        sb.append(" WHERE dataScene.AppID = dataArrangeDistinct.AppID AND dataScene.ExamPlanInfoID = dataArrangeDistinct.ExamPlanInfoID");
        sb.append(" AND dataSubject.AppID = dataArrangeDistinct.AppID AND dataSubject.ExamPlanInfoID = dataArrangeDistinct.ExamPlanInfoID");
        sb.append(" AND dataRoom.AppID = dataArrangeDistinct.AppID AND dataRoom.ExamSiteID = \'").append(siteId).append("\'");
        sb.append(" AND dataPlan.AppID = dataPlan.AppID");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }


    public String findExamineePlanInfoList(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder();
        List<String> examPlanInfoIds = (List<String>) parameter.get("examPlanInfoIds");
        List<String> examineeIds = (List<String>) parameter.get("examineeIds");
        Integer submited = (Integer) parameter.get("submited");//考生考试管理状态
        Integer testPaperCreated = (Integer) parameter.get("testPaperCreated");//考试计划系统状态
        String timeNow = (String) parameter.get("timeNow");//当前时间

        sb.append("SELECT \n" +
                "planInfo.AppID as appID,\n" +
                "planInfo.ExamPlanInfoID as examPlanInfoID,\n" +
                "planInfo.ExamPlanName as examPlanName,\n" +
                "arrange.PlanBeginTime as planBeginTime,\n" +
                "arrange.PlanEndTime as planEndTime,\n" +
                "examinee.ExamineeID as examineeID,\n" +
                "examinee.ExamineeName as examineeName,\n" +
                "monitors.SysState as sysState,\n" +
                "arrange.ExamSubjectID as examSubjectID,\n" +
                "subject.SubjectName as examSubjectName,\n" +
                "planInfo.ExamMode as examMode  \n" +
                "FROM ExamPlanInfo planInfo \n" +
                "INNER JOIN Examinee examinee ON planInfo.ExamPlanInfoID = examinee.ExamPlanInfoID \n" +
                "INNER JOIN ExamineeArrange arrange ON planInfo.ExamPlanInfoID = arrange.ExamPlanInfoID \n" +
                "INNER JOIN ExamSubject subject ON arrange.ExamSubjectID = subject.ExamSubjectID \n" +
                "INNER JOIN ExamingMonitor monitors ON planInfo.ExamPlanInfoID = monitors.ExamPlanInfoID \n" +
                "WHERE arrange.ExamineeID = examinee.ExamineeID \n" +
                "AND subject.AppID = arrange.AppID \n" +
                "AND subject.ExamPlanInfoID = arrange.ExamPlanInfoID \n" +
                "AND monitors.AppID = arrange.AppID \n" +
                "AND monitors.ExamSubjectID = arrange.ExamSubjectID \n" +
                "AND monitors.ExamineeID = arrange.ExamineeID ");

        if(!StringUtils.isEmpty(timeNow)){
            sb.append(" AND arrange.PlanBeginTime < \'").append(timeNow).append("\'")
                    .append(" AND arrange.PlanEndTime > \'").append(timeNow).append("\'");
        }
        sb.append(" AND monitors.SysState != ").append(submited);
        sb.append(" AND planInfo.SysState = ").append(testPaperCreated);
        sb.append(" AND planInfo.ExamPlanInfoID IN (");
        if(examPlanInfoIds != null && !examPlanInfoIds.isEmpty()){
            StringBuffer examPlanInfoIdBuff = new StringBuffer();
            for (String examPlanInfoId : examPlanInfoIds){
                examPlanInfoIdBuff.append("\'").append(examPlanInfoId+"\' ,");
            }
            sb.append(examPlanInfoIdBuff.substring(0,examPlanInfoIdBuff.length()-1));
        }
        sb.append(")");

        sb.append(" AND examinee.ExamineeID IN (");
        if(examineeIds != null && !examineeIds.isEmpty()){
            StringBuffer examineeIdBuff = new StringBuffer();
            for (String examineeId : examineeIds){
                examineeIdBuff.append("\'").append(examineeId+"\' ,");
            }
            sb.append(examineeIdBuff.substring(0,examineeIdBuff.length()-1));
        }
        sb.append(")");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }
}
